﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using JTest.DTO;
using JTest.DAO;
using System.IO;
using System.Text.RegularExpressions;

namespace JTest.BUS
{
    class DataImportBUS
    {
        public static int insertData(List<QuestionDTO> list, string db, string type, int level)
        {
            string connStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + AppDomain.CurrentDomain.BaseDirectory + "App_Data\\" + db;
            int totalRecord = 0;
            foreach (QuestionDTO q in list)
            {
                bool rs = QuestionDAO.insert(connStr, type, level, q);
                if (rs == true)
                {
                    totalRecord++;
                }
            }
            return totalRecord;
        }

        public static string createNewDbase(string dbName)
        {
            string savePath;
            Stream objStream = null;
            FileStream objFileStream = null;
            try
            {
                byte[] abytResource;
                System.Reflection.Assembly objAssembly =
            System.Reflection.Assembly.GetExecutingAssembly();

                objStream = objAssembly.GetManifestResourceStream("JTest.template.jtest_template.mdb");
                abytResource = new Byte[objStream.Length];
                objStream.Read(abytResource, 0, (int)objStream.Length);

                savePath = AppDomain.CurrentDomain.BaseDirectory + "App_Data/" + dbName + ".mdb";
                objFileStream = new FileStream(savePath, FileMode.Create);
                objFileStream.Write(abytResource, 0, (int)objStream.Length);
                objFileStream.Close();
            }
            catch (Exception ex)
            {
                throw ;
            }
            finally
            {
                if (objFileStream != null)
                {
                    objFileStream.Close();
                    objFileStream = null;
                }
                if (objStream != null)
                {
                    objStream = null;
                }
            }

            return dbName + ".mdb";
        }

        public static List<QuestionDTO> getQuestionListFromExcel(string filepath)
        {
            List<QuestionDTO> list = new List<QuestionDTO>();

            string Path = filepath;
            // initialize the Excel Application class
            ApplicationClass app = new ApplicationClass();
            // create the workbook object by opening the excel file.
            Workbook workBook = app.Workbooks.Open(Path,
                                                         0,
                                                         true,
                                                         5,
                                                         "",
                                                         "",
                                                         true,
                                                         XlPlatform.xlWindows,
                                                         "\t",
                                                         false,
                                                         false,
                                                         0,
                                                         true,
                                                         1,
                                                         0);
            // get the active worksheet using sheet index 
            Worksheet wsQuestion = (Worksheet)workBook.Worksheets[1];
            // sheet row,column index .
            int rowQIndex = 1;
            int colQIndex = 1;
            int colAIndex = 9;
            try
            {
                while (((Range)wsQuestion.Cells[rowQIndex, colQIndex]).Value2 != null)
                {
                    string question = ((Range)wsQuestion.Cells[rowQIndex, colQIndex]).Value2.ToString();
                    string ansA = ((Range)wsQuestion.Cells[rowQIndex + 2, colQIndex]).Value2.ToString();
                    string ansB = ((Range)wsQuestion.Cells[rowQIndex + 3, colQIndex]).Value2.ToString();
                    string ansC = ((Range)wsQuestion.Cells[rowQIndex + 4, colQIndex]).Value2.ToString();
                    string ansD = ((Range)wsQuestion.Cells[rowQIndex + 5, colQIndex]).Value2.ToString();
                    string answer = ((Range)wsQuestion.Cells[rowQIndex + 6, colAIndex]).Value2.ToString();

                    // jump to next question
                    rowQIndex += 8;

                    // check blank data
                    if (question.Trim().Length == 0 || ansA.Trim().Length == 0
                        || ansB.Trim().Length == 0 || ansC.Trim().Length == 0
                        || ansD.Trim().Length == 0 || answer.Trim().Length == 0)
                    {
                        // not add this question
                        continue;
                    }

                    // create dto obj add to result list
                    QuestionDTO dto = new QuestionDTO();
                    dto.Question = Regex.Replace(question, "問[0-9]*\\.", "");
                    dto.AnswerA = Regex.Replace(ansA, "[ABCD]{1}.", "");
                    dto.AnswerB = Regex.Replace(ansB, "[ABCD]{1}.", "");
                    dto.AnswerC = Regex.Replace(ansC, "[ABCD]{1}.", "");
                    dto.AnswerD = Regex.Replace(ansD, "[ABCD]{1}.", "");
                    dto.Answer = answer[4];
                    list.Add(dto);
                  
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                workBook.Close(false, false, false);
                app.Quit();
                wsQuestion = null;
                workBook = null;
                app = null;
                System.GC.Collect();
            }

            return list;
        }
    }
}
